﻿using System;
using System.Collections;
using System.Collections.Generic;
using System.Data.SqlClient;
using System.Linq;
using System.Text;


namespace LoadTestReport.DAO
{
    public class DataReader
    {
        public static System.Collections.ArrayList getAllTestRuns()
        {
            ArrayList testArray = new ArrayList();
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT TAB1.LoadTestRunId" +
                              ", TAB1.LoadTestName" +
                              ", TAB1.StartTime" +
                              ", TAB1.EndTime" +
                              ", TAB1.RunDuration" +
                              ", TAB1.Outcome" +
                              ", TAB2.CumulativeValue" +
                          " FROM dbo.LoadTestRun TAB1, dbo.LoadTestScenarioSummary TAB2" +
                          " where TAB2.LoadTestRunId=TAB1.LoadTestRunId" +
                          " and TAB2.CounterName = 'User Load' and TAB2.ScenarioName = '_Total' order by TAB1.StartTime desc";

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<String,String> dict = new Dictionary<String,String>();

                                String runID = dr.GetValue(0).ToString();
                                dict.Add("runID", runID);
                                String testName = dr.GetValue(1).ToString();
                                dict.Add("testName", testName);
                                String startTime = dr.GetValue(2).ToString();
                                dict.Add("startTime", startTime);
                                String endTime = dr.GetValue(3).ToString();
                                dict.Add("endTime", endTime);
                                String runDuration = dr.GetValue(4).ToString();
                                dict.Add("runDuration", runDuration);
                                String outcome = dr.GetValue(5).ToString();
                                dict.Add("outcome", outcome);
                                String users = dr.GetValue(6).ToString();
                                dict.Add("users", users);
                                testArray.Add(dict);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        internal static ArrayList getTestRunInformation(int SelectedRunID)
        {
            ArrayList testArray = new ArrayList();
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT LoadTestRunId ,LoadTestName ,Description ,StartTime ,EndTime ,RunDuration ,WarmupTime ,RunSettingUsed ,Outcome ,CooldownTime"+
                                  " FROM dbo.LoadTestRun " +
                                  " where LoadTestRunId = " + SelectedRunID;

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<String, String> dict = new Dictionary<String, String>();

                                String runID = dr.GetValue(0).ToString();
                                dict.Add("runID", runID);
                                String testName = dr.GetValue(1).ToString();
                                dict.Add("testName", testName);
                                String description = dr.GetValue(2).ToString();
                                dict.Add("description", description);

                                String startTime = dr.GetValue(3).ToString();
                                dict.Add("startTime", startTime);

                                String endTime = dr.GetValue(4).ToString();
                                dict.Add("endTime", endTime);
                                
                                String runDuration = dr.GetValue(5).ToString();
                                dict.Add("runDuration", runDuration);

                                String WarmupTime = dr.GetValue(6).ToString();
                                dict.Add("WarmupTime", WarmupTime);

                                String RunSettingUsed = dr.GetValue(7).ToString();
                                dict.Add("RunSettingUsed", RunSettingUsed);

                                String outcome = dr.GetValue(8).ToString();
                                dict.Add("outcome", outcome);

                                String CooldownTime = dr.GetValue(9).ToString();
                                dict.Add("CooldownTime", CooldownTime);

                                testArray.Add(dict);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        internal static ArrayList getOverallResults(int p)
        {
            ArrayList testArray = new ArrayList();
            String[] queries = new String[5];

            queries[0] = "select CounterName,CumulativeValue from dbo.LoadTestScenarioSummary where ScenarioName = '_Total' and CounterName = 'User Load' and LoadTestRunId = "+p;
            queries[1] = "select CounterName,CumulativeValue from dbo.LoadTestTestCaseSummary where ScenarioName = '_Total' and (CounterName = 'Tests/Sec' or CounterName='Failed Tests' or CounterName='Avg. Test Time' )and LoadTestRunId = "+p;
            queries[2] = "select CounterName,CumulativeValue from dbo.LoadTestTransactionSummary where ScenarioName = '_Total' and (CounterName = 'Transactions/Sec' or CounterName='Avg. Transaction Time' )and LoadTestRunId = "+p;
            queries[3] = "select CounterName,CumulativeValue from dbo.LoadTestPageSummary where ScenarioName = '_Total' and (CounterName = 'Pages/Sec' or CounterName='Avg. Page Time' )and LoadTestRunId = "+p;
            queries[4] = "select CounterName,CumulativeValue from dbo.LoadTestRequestSummary where ScenarioName = '_Total' and (CounterName = 'Requests/Sec' or CounterName='Failed Requests' or CounterName='Avg. Response Time' or CounterName='Avg. Content Length')and LoadTestRunId = "+p;
                   
            try
            {
                for (int i = 0; i < queries.Length; i++)
                {
                    using (SqlConnection conn = new SqlConnection(TestParent.connString))
                    {
                      
                        using (SqlCommand cmd = new SqlCommand(queries[i], conn))
                        {
                            conn.Open();
                            using (SqlDataReader dr = cmd.ExecuteReader())
                            {
                                while (dr.Read())
                                {
                                    Dictionary<String, String> dict = new Dictionary<String, String>();

                                    String CounterName = dr.GetValue(0).ToString();
                                    String CounterValue = dr.GetValue(1).ToString();
                                    dict.Add(CounterName, CounterValue);

                                    testArray.Add(dict);
                                }
                            }
                        }                   
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        internal static ArrayList getTop5SlowestPages(int p)
        {
            ArrayList testArray = new ArrayList();
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT TOP 5 TestCaseName ,RequestUri ,CumulativeValue " +
                                  " FROM dbo.LoadTestPageSummary " +
                                  " where LoadTestRunId = "+ p +" and CounterName = 'Avg. Page Time' and TestCaseName != '_Total' order by CumulativeValue desc";                   

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<String, String> dict = new Dictionary<String, String>();

                                String TestCaseName = dr.GetValue(0).ToString();
                                String RequestUri = dr.GetValue(1).ToString();
                                String CumulativeValue = dr.GetValue(2).ToString();

                                dict.Add(TestCaseName + "-" + RequestUri, CumulativeValue);

                                testArray.Add(dict);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        internal static ArrayList getTop5SlowestTests(int p)
        {
            ArrayList testArray = new ArrayList();
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT TOP 5 TestCaseName , CumulativeValue " +
                                  " FROM dbo.LoadTestTestCaseSummary " +
                                  " where LoadTestRunId = "+p+" and CounterName = 'Avg. Test Time' and TestCaseName != '_Total'";
                    
                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<String, String> dict = new Dictionary<String, String>();

                                String TestCaseName = dr.GetValue(0).ToString();
                                String CumulativeValue = dr.GetValue(1).ToString();
                                dict.Add(TestCaseName, CumulativeValue);

                                testArray.Add(dict);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        internal static ArrayList getPageResults(int p)
        {
            ArrayList testArray = new ArrayList();
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT ScenarioName ,TestCaseName ,RequestUri ,PageCount ,Minimum ,Average ,Percentile90 ,Percentile95 ,Maximum" +
                                  " FROM dbo.LoadTestPageResults " +
                                  " where LoadTestRunId = " + p;
                    
                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<String, String> dict = new Dictionary<String, String>();

                                String ScenarioName = dr.GetValue(0).ToString();
                                dict.Add("ScenarioName", ScenarioName);

                                String TestCaseName = dr.GetValue(1).ToString();
                                dict.Add("TestCaseName", TestCaseName);

                                String RequestUri = dr.GetValue(2).ToString();
                                dict.Add("RequestUri", RequestUri);

                                String PageCount = dr.GetValue(3).ToString();
                                dict.Add("PageCount", PageCount);

                                String Minimum = dr.GetValue(4).ToString();
                                dict.Add("Minimum", Minimum);

                                String Average = dr.GetValue(5).ToString();
                                dict.Add("Average", Average);

                                String Percentile90 = dr.GetValue(6).ToString();
                                dict.Add("Percentile90", Percentile90);

                                String Percentile95 = dr.GetValue(7).ToString();
                                dict.Add("Percentile95", Percentile95);

                                String Maximum = dr.GetValue(8).ToString();
                                dict.Add("Maximum", Maximum);
                                
                                testArray.Add(dict);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        internal static ArrayList getSystemUnderTestResources(int p)
        {
            ArrayList testArray = new ArrayList();
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT [MachineName] ,[CategoryName] ,[CounterName] ,[InstanceName] ,[CumulativeValue]" +
                      " FROM [dbo].[LoadTestComputedCounterSummary] " +
                      " where LoadTestRunId = "+p+" and ( CounterName='Available MBytes' or " +
                      " (CounterName = '% Processor Time' and CategoryName='Processor')) " +
                      " and MachineName in (SELECT MachineName FROM dbo.LoadTestSystemUnderTest where LoadTestRunId = " + p + ") " +
                      " order by MachineName ";


  
                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<String, String> dict = new Dictionary<String, String>();

                                String MachineName = dr.GetValue(0).ToString();
                                dict.Add("MachineName", MachineName);

                                String CategoryName = dr.GetValue(1).ToString();
                                dict.Add("CategoryName", CategoryName);

                                String CounterName = dr.GetValue(2).ToString();
                                dict.Add("CounterName", CounterName);

                                String InstanceName = dr.GetValue(3).ToString();
                                dict.Add("InstanceName", InstanceName);

                                String CumulativeValue = dr.GetValue(4).ToString();
                                dict.Add("CumulativeValue", CumulativeValue);

                                testArray.Add(dict);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        private static ArrayList getMessageSubTypes(int p)
        {
            ArrayList testArray = new ArrayList();
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT distinct SubType FROM [dbo].[LoadTestMessageSummary] where LoadTestRunId = "+p;



                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                Dictionary<String, String> dict = new Dictionary<String, String>();

                                String SubType = dr.GetValue(0).ToString();
                                dict.Add("SubType", SubType);                                

                                testArray.Add(dict);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            return testArray;
        }

        internal static ArrayList getErrorSummary(int p)
        {
            ArrayList testArray = new ArrayList();
            ArrayList messageSubTypes = getMessageSubTypes(p);

            for (int i = 0; i < messageSubTypes.Count; i++)
            {
                try
                {
                    using (SqlConnection conn = new SqlConnection(TestParent.connString))
                    {
                        String query = "select MessageType,SubType,MessageText from [dbo].[LoadTestMessageSummary] where MessageId = (SELECT MAX(MessageId) FROM [dbo].[LoadTestMessageSummary] "+
                                        " where LoadTestRunId = " + p + " and SubType = '" + ((Dictionary<String,String>)messageSubTypes[i])["SubType"] + "') and LoadTestRunId = " + p;

                        using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                        {
                            conn.Open();
                            using (SqlDataReader dr = cmd.ExecuteReader())
                            {
                                while (dr.Read())
                                {
                                    Dictionary<String, String> dict = new Dictionary<String, String>();

                                    String MessageType = dr.GetValue(0).ToString();
                                    dict.Add("MessageType", MessageType);

                                    String SubType = dr.GetValue(1).ToString();
                                    dict.Add("SubType", SubType);

                                    String MessageText = dr.GetValue(2).ToString();
                                    dict.Add("MessageText", MessageText);

                                    testArray.Add(dict);
                                }
                            }
                        }
                    }
                }
                catch (Exception ex)
                {
                    Console.Write(ex.Message);
                    return null;
                }
            }

            return testArray;
        }

        internal static ArrayList getGraphData(String machine,int p, String counter, String category)
        {
            ArrayList testList = new ArrayList();
            ArrayList xList = new ArrayList();
            ArrayList yList = new ArrayList();
            DateTime firstDt = new DateTime();

            bool isFirstOccured = false;
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT IntervalEndTime, ComputedValue FROM dbo.LoadTestComputedCounterSample where LoadTestRunId = "+p+" and MachineName = '"
                        + machine + "' and CounterName='" + counter + "' and (InstanceName='__Total__' or InstanceName='_Global_' or InstanceName='_Total' or InstanceName='systemdiagnosticsperfcounterlibsingleinstance') and " +
                        "CategoryName = '" + category+"'";

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                DateTime IntervalEndTime = dr.GetDateTime(0);
                                double val = Math.Round(double.Parse(dr.GetValue(1).ToString()),2);

                                if(!isFirstOccured)
                                {
                                    firstDt = IntervalEndTime;
                                    isFirstOccured = true;
                                }

                                TimeSpan t = IntervalEndTime - firstDt;

                                xList.Add(t.TotalMinutes);
                                yList.Add(val);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
            testList.Add(xList);
            testList.Add(yList);
            return testList;
        }

        internal static ArrayList getMachinesInvolved(int p, String counter)
        {
            ArrayList testList = new ArrayList();           
            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT MachineName FROM dbo.LoadTestSystemUnderTest where LoadTestRunId = "+p;

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                String machine = dr.GetValue(0).ToString();

                                testList.Add(machine);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }
           
            return testList;
        }

        internal static ArrayList getValidCounters(int p)
        {
            ArrayList testList = new ArrayList();
            ArrayList counterList = new ArrayList();
            ArrayList categoryList = new ArrayList();

            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT distinct CounterName,CategoryName FROM dbo.LoadTestComputedCounterSummary where LoadTestRunId = " + p;

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                String counter = dr.GetValue(0).ToString();
                                String category = dr.GetValue(1).ToString();

                                counterList.Add(counter);
                                categoryList.Add(category);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }

            testList.Add(counterList);
            testList.Add(categoryList);

            return testList;
        }


        internal static ArrayList getCounterCategories(int p)
        {           
            ArrayList categoryList = new ArrayList();

            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT distinct CategoryName FROM dbo.LoadTestComputedCounterSummary where LoadTestRunId = " + p;

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                String category = dr.GetValue(0).ToString();                                
                                categoryList.Add(category);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }

            return categoryList;
        }

        internal static ArrayList getAllCounters_for_Category(int p,String CategoryName)
        {
            ArrayList counterList = new ArrayList();

            try
            {
                using (SqlConnection conn = new SqlConnection(TestParent.connString))
                {
                    String query = "SELECT distinct CounterName FROM dbo.LoadTestComputedCounterSummary where LoadTestRunId = " + p
                        + " and CategoryName = '"+CategoryName+"'";

                    using (SqlCommand cmd = new SqlCommand(query.ToString(), conn))
                    {
                        conn.Open();
                        using (SqlDataReader dr = cmd.ExecuteReader())
                        {
                            while (dr.Read())
                            {
                                String counter = dr.GetValue(0).ToString();
                                counterList.Add(counter);
                            }
                        }
                    }
                }
            }
            catch (Exception ex)
            {
                Console.Write(ex.Message);
                return null;
            }

            return counterList;
        }
    }
}
